package chase.myrewards;

import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.HashMap;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import com.google.appengine.api.rdbms.AppEngineDriver;
import com.google.appengine.api.utils.SystemProperty;

import chase.myrewards.beans.*;


/**
 * Servlet that handles queries and processing for location module.
 * Take a location and find closest instances of certain MCCs within a certain radius.
 * 
 * @author Meena Abdou
 * @date April 27, 2013
 */

public class LocationServlet extends HttpServlet implements Serializable {

	/**
	 * This number must be unique for every class. And it can never change.
	 * You have to generate it automatically by clicking on the class name and
	 * then hitting Ctrl+1.  Then click on Add generated serial version ID. Then
	 * copy these comments on top of the generated id.
	 * 
	 * @serial serialVersionUID
	 */
	private static final long serialVersionUID = 5676756629974048837L;

	private static final Logger log = Logger.getLogger(AppLoginServlet.class.getName());

	private Customer cust;
	private Account account;
	private String myLat;
	private String myLon;
	private String requestType;
	private ArrayList<LatLon> locations;
	private Connection c;

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		cust = null;
		account = null;
		myLat = null;
		myLon = null;
		locations = new ArrayList<LatLon>();
		c = null;
		
		log.info("Session Id is " + request.getSession().getId());

		// The app is running on App Engine
		if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Production) {
			
			try {
				// Database connection
				DriverManager.registerDriver(new AppEngineDriver());
				c = DriverManager.getConnection("jdbc:google:rdbms://myrewards2013:myrewardsdatabase/myrewards");		

				// Get parameters from session objects
				cust = (Customer) request.getSession().getAttribute("customer");
				account = (Account) request.getSession().getAttribute("accountinfo");

				// Redo this
				if (cust == null || account == null){
					log.info("Customer or Account is null for this session");
					throw new Exception();
				}

				boolean favorites = cust.getFavorites();
				boolean recent = cust.getRecent();

				// Get request parameters
				myLat = request.getParameter("myLat");
				myLon = request.getParameter("myLon");
				requestType = request.getParameter("requestType");

				log.info("requestType is " + requestType);
				
				//System.out.println("myLat is " + myLat);
				//System.out.println("myLon is " + myLon);

				/* -------------  Added by Meena ------------------------------------------- */ 

				if (requestType.equalsIgnoreCase("default")){
					
					// If favorites and recent, then send favorite and recent locations to the client
					if (favorites && recent){
						log.info("favorites and recent");
						getFavorites();
						getRecent();
					}

					// Else if just favorites
					else if (favorites){
						log.info("just favorites");
						getFavorites();
					}

					// Else if just recent
					else if (recent){
						log.info("just recent");
						getRecent();
					}
				}

				else if(requestType.equalsIgnoreCase("earn")){
					getEarningLocations();
				}

				else if(requestType.equalsIgnoreCase("spend")){
					getSpendingLocations();
				}
				/* --------------------------------------------------------------------------- */
			}
			catch (SQLException e) {
				log.log(Level.SEVERE, "SQLException", e);
				locations = null;
			}
			catch(IOException e){
				log.log(Level.SEVERE, "IOException", e);
			}
			catch (Exception e){
				log.log(Level.SEVERE, "Exception", e);
				locations = null;
			}
			finally {
				if (c != null){ 
					try {
						c.close();
					} 
					catch (SQLException ignore) {}
				}
			}
		}

		// Development server
		else {
			
			try {
				myLat = request.getParameter("myLat");
				myLon = request.getParameter("myLon");        
				//System.out.println("myLat is " + myLat);
				//System.out.println("myLon is " + myLon);

				HashMap<String, String> params = new HashMap<String, String>();
				params.put("category", "restaurant");

				String query = Queries.PLACES_QUERY(myLat, myLon, params);
				NodeList nodes = getPlaces(query);
				//System.out.println("nodes is " + nodes);
				//System.out.println("nodes length is " + nodes.getLength());

				// For each result element
				for (int i = 0; i < nodes.getLength(); i++) {

					Node node = nodes.item(i);
					if (node.getNodeType() == Node.ELEMENT_NODE) {
						LatLon l = new LatLon();
						Node geometry = findSubNode("geometry", node);
						//log.info("geometry is " + geometry);
						Node location = findSubNode("location", geometry);
						//log.info("location is " + location);
						Node lat = findSubNode("lat", location);
						//log.info("node's lat is " + lat);
						//log.info("node's lat value is " + lat.getTextContent());
						Node lon = findSubNode("lng", location);
						//log.info("node's lon is " + lon);
						//log.info("node's lon value is " + lon.getTextContent());
						l.setLatitude(lat.getTextContent());
						l.setLongitude(lon.getTextContent());

						// add description of programs or whatever into l

						// Put l into the list of LatLons
						locations.add(l);
					}
				}
			}
			catch (Exception e){
				log.log(Level.SEVERE, "SQLException", e);
				locations = null;
			}
		}
		
		// Send data back to client - in the form of a list of LatLon objects
		try {
			response.setContentType("application/octet-stream");
			ObjectOutputStream out = new ObjectOutputStream(response.getOutputStream());
			out.writeObject(locations);
			out.flush();
			out.close();
		}
		catch(IOException i){
			log.log(Level.SEVERE, "SQLException", i);
		}
		return;
	}
	
	/**
	 * Get the customer's favorite locations and put them in the locations array list.
	 * 
	 * @param myLat
	 * @param myLon
	 * @throws SAXException 
	 * @throws ParserConfigurationException 
	 * @throws IOException 
	 */
	public void getFavorites() throws IOException, ParserConfigurationException, SAXException{
		try{
			java.sql.Statement stmt =  c.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery(Queries.FAVORITE_LOCATIONS(account.getAccountNum()));

			// Get all the favorite store names in the form of name1|name2|name3|...
			String names = "";
			while(rs.next()){
				names += "\"" + rs.getString("name") + "\"" + "|";
				log.info(names);
			}
			names = names.substring(0, names.length() - 1);
			log.info(names);

			HashMap<String, String> params = new HashMap<String, String>();
			params.put("name", names);

			// Run places query
			String query = Queries.PLACES_QUERY(myLat, myLon, params);
			log.info("Querying Places with " + query);
			NodeList nodes = getPlaces(query);

			//insertPlaces(nodes);
			for (int i = 0; i < nodes.getLength(); i++) {
				Node node = nodes.item(i);
				if (node.getNodeType() == Node.ELEMENT_NODE) {
					Node name = findSubNode("name", node);
					Node address = findSubNode("formatted_address",node);
					Node vicinity = findSubNode("vicinity",node);
					Node geometry = findSubNode("geometry", node);
					Node location = findSubNode("location", geometry);
					Node lat = findSubNode("lat", location);
					Node lon = findSubNode("lng", location);

					LatLon l = new LatLon();
					l.setName(name.getTextContent());
					l.setLatitude(lat.getTextContent());
					l.setLongitude(lon.getTextContent());
					
					if (address != null){
						l.setAddress(address.getTextContent());
					}
					else if (vicinity != null){
						l.setAddress(vicinity.getTextContent());
					}

					// Put l into the list of LatLons
					locations.add(l);
				}
			} // End of for loop

		}
		catch (Exception e){
			log.log(Level.SEVERE, "Exception", e);
		}
	}

	/**
	 * Get the customer's recent locations and put them in the locations array list.
	 * @param myLat
	 * @param myLon
	 */
	public void getRecent(){
		try{
			java.sql.Statement stmt =  c.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery(Queries.RECENT_LOCATIONS(account.getAccountNum()));

			// Get all the recently used store names in the form of name1|name2|name3|...
			String names = "";
			while(rs.next()){
				names += "\"" + rs.getString("store_name") + "\"" + "|";
				log.info(names);
			}
			names = names.substring(0, names.length() - 1);
			log.info(names);

			HashMap<String, String> params = new HashMap<String, String>();
			params.put("name", names);

			// Run places query
			String query = Queries.PLACES_QUERY(myLat, myLon, params);
			log.info("Querying Places with " + query);
			NodeList nodes = getPlaces(query);

			// Insert places into locations list
			for (int i = 0; i < nodes.getLength(); i++) {
				Node node = nodes.item(i);
				if (node.getNodeType() == Node.ELEMENT_NODE) {
					Node name = findSubNode("name", node);
					Node address = findSubNode("formatted_address",node);
					Node vicinity = findSubNode("vicinity",node);
					Node geometry = findSubNode("geometry", node);
					Node location = findSubNode("location", geometry);
					Node lat = findSubNode("lat", location);
					Node lon = findSubNode("lng", location);

					LatLon l = new LatLon();
					l.setName(name.getTextContent());
					l.setLatitude(lat.getTextContent());
					l.setLongitude(lon.getTextContent());
					
					if (address != null){
						l.setAddress(address.getTextContent());
					}
					else if (vicinity != null){
						l.setAddress(vicinity.getTextContent());
					}

					// Put l into the list of LatLons
					locations.add(l);
				}
			} // End of for loop
		}
		catch (Exception e){
			log.log(Level.SEVERE, "Exception", e);
		}
	}

	/**
	 * Get locations at which user may earn more rewards points.
	 * Put the locations in the locations array list.
	 * 
	 * @throws IOException 
	 * @throws SAXException 
	 * @throws ParserConfigurationException 
	 */
	public void getEarningLocations() throws ParserConfigurationException, SAXException, IOException{
		// Earning rewards
		// Get program-ids associated with account_num
		// Get mcc and description for each program_id
		// Get list of addresses and category for these mcc
		// Get closest of these to client
		// Display descriptions

		String num = account.getAccountNum();

		try{
			//Getting all program_id's for the selected account number 
			java.sql.Statement stmt1 =  c.createStatement();
			java.sql.ResultSet rs1 = stmt1.executeQuery(Queries.PROGRAM_ID(num));

			while(rs1.next()){
				//Get program description and mcc for the program_id's
				java.sql.Statement stmt2 = c.createStatement();
				java.sql.ResultSet rs2 = stmt2.executeQuery(Queries.DESCRIPTION_MCC_PROGRAM(rs1.getString("program_id")));

				while(rs2.next()){
					// Build a set of categories for search criteria
					java.sql.Statement stmt3 = c.createStatement();
					java.sql.ResultSet rs3 = stmt3.executeQuery(Queries.CATEGORY(rs2.getString("mcc")));

					String categories = "";
					while(rs3.next()){
						categories += "\"" + rs3.getString("category") + "\"" + "|";
					}
					
					HashMap<String, String> params = new HashMap<String, String>();
					
					if (!categories.isEmpty()){
						categories = categories.substring(0, categories.length() - 1);
						params.put("category", categories);
					}
					
					NodeList nodes = getPlaces(Queries.PLACES_QUERY(myLat, myLon, params));

					//Adding places from query resultant to locations array

					for (int i = 0; i < nodes.getLength(); i++) {
						Node node = nodes.item(i);
						if (node.getNodeType() == Node.ELEMENT_NODE) {
							Node name = findSubNode("name", node);
							Node address = findSubNode("formatted_address",node);
							Node vicinity = findSubNode("vicinity",node);
							Node geometry = findSubNode("geometry", node);
							Node location = findSubNode("location", geometry);
							Node lat = findSubNode("lat", location);
							Node lon = findSubNode("lng", location);

							LatLon l = new LatLon();
							l.setName(name.getTextContent());
							l.setLatitude(lat.getTextContent());
							l.setLongitude(lon.getTextContent());
							l.insertProgramDes(rs2.getString("program.description"));
							
							if (address != null){
								l.setAddress(address.getTextContent());
							}
							else if (vicinity != null){
								l.setAddress(vicinity.getTextContent());
							}

							// Put l into the list of LatLons
							locations.add(l);
						}
					} // End of for loop
				}  // End of while(rs2.next())
			} // End of while(rs1.next())
		}  // End of try
		catch(Exception e){
			log.log(Level.SEVERE, "Exception", e);
		}
	}

	/**
	 * Get locations at which user may spend their rewards points.
	 * Put the locations in locations array list.
	 */
	public void getSpendingLocations(){
		// Spending rewards
		// Get promo-ids and eligibility associated with account_num
		// Get mcc and description for each promo_id
		// Get list of addresses and category for these mcc
		// Get closest of these to client
		// Display descriptions

		String num = account.getAccountNum();

		try { 
			//Getting all promo_id's for the selected account number 
			java.sql.Statement stmt1 =  c.createStatement();
			java.sql.ResultSet rs1 = stmt1.executeQuery(Queries.PROMO_ID(num));

			while(rs1.next() && rs1.getBoolean("eligible")){

				//getting  promo description and mcc for the promoid's
				java.sql.Statement stmt2 =  c.createStatement();
				java.sql.ResultSet rs2 = stmt2.executeQuery(Queries.DESCRIPTION_MCC_PROMO(rs1.getString("promo_id")));

				// Adding promotion descriptions to promoDes array
				while(rs2.next()){

					java.sql.Statement stmt3 =  c.createStatement();
					java.sql.ResultSet rs3 = stmt3.executeQuery(Queries.CATEGORY(rs2.getString("mcc")));

					// Building a set of categories for search criteria
					String categories = "";
					while(rs3.next()){
						categories += "\"" + rs3.getString("category") + "\"" + "|";
					}
					categories = categories.substring(0, categories.length() - 1);

					HashMap<String, String> params = new HashMap<String, String>();
					params.put("category", categories);

					NodeList nodes = getPlaces(Queries.PLACES_QUERY(myLat, myLon, params)); 

					// Adding places from query resultant to locations array

					for (int i = 0; i < nodes.getLength(); i++) {
						Node node = nodes.item(i);
						if (node.getNodeType() == Node.ELEMENT_NODE) {
							Node name = findSubNode("name", node);
							Node address = findSubNode("formatted_address",node);
							Node vicinity = findSubNode("vicinity",node);
							Node geometry = findSubNode("geometry", node);
							Node location = findSubNode("location", geometry);
							Node lat = findSubNode("lat", location);
							Node lon = findSubNode("lng", location);

							LatLon l = new LatLon();
							l.setName(name.getTextContent());
							l.setLatitude(lat.getTextContent());
							l.setLongitude(lon.getTextContent());
							l.insertPromoDes(rs2.getString("promotion.description"));
							
							if (address != null){
								l.setAddress(address.getTextContent());
							}
							else if (vicinity != null){
								l.setAddress(vicinity.getTextContent());
							}

							// Put l into the list of LatLons
							locations.add(l);
						}
					} // End of for loop
				}  // End of while(rs1.next())
			}  // End of while(rs2.next())
		} // End of try
		catch (Exception e){
			log.log(Level.SEVERE, "Exception", e);
		}
	}

	/**
	 * Query the Google Places API with the specified query.
	 * For an example XML document that may be returned see https://developers.google.com/places/documentation/search#PlaceSearchResults
	 * Parse the output and return a NodeList containing the results of the query
	 * 
	 * @param The query to be sent to Google Places
	 * @return NodeList containing result elements
	 * @throws MalformedURLException 
	 * @throws SAXException 
	 * @throws ParserConfigurationException 
	 * @throws IOException   
	 */
	public NodeList getPlaces(String query) throws IOException, ParserConfigurationException, SAXException {
		// Search for nearby mcc in nearby locations - query Places API
		URL url = new URL(query); //category here should be a query resultant
		HttpURLConnection connection = (HttpURLConnection) url.openConnection();
		connection.setDoOutput(true);
		connection.setRequestMethod("GET");

		// Parse the XML output - The DOM way
		DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
		DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
		Document doc = dBuilder.parse(connection.getInputStream());
		doc.getDocumentElement().normalize();

		// Get all the result elements
		NodeList nodes = doc.getElementsByTagName("result");
		return nodes;		
	}	

	/**
	 * Find the named subnode in a node's immediate sublist.
	 * <ul>
	 * <li>Ignores comments and processing instructions.
	 * <li>Ignores TEXT nodes (likely to exist and contain ignorable whitespace, if not validating.
	 * <li>Ignores CDATA nodes and EntityRef nodes.
	 * <li>Examines element nodes to find one with the specified name.
	 * </ul>
	 * @param name  The tag name for the element to find
	 * @param node  The element node to start searching from
	 * @return The Node found
	 * @throws Exception 
	 */
	public Node findSubNode(String name, Node node) throws Exception {
		if (node.getNodeType() != Node.ELEMENT_NODE) {
			throw new Exception("Error: Search node not of element type");
		}

		if (! node.hasChildNodes()) return null;

		NodeList list = node.getChildNodes();
		for (int i=0; i < list.getLength(); i++) {
			Node subnode = list.item(i);
			if (subnode.getNodeType() == Node.ELEMENT_NODE) {
				if (subnode.getNodeName().equals(name)) 
					return subnode;
			}
		}
		return null;
	}

}
